L, ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, QUOTED_IDENTIFIER ON 
SET  NUMERIC_ROUNDABORT OFF
GO
:setvar DatabaseName "MetaCollection2"
:setvar PrimaryFilePhysicalName "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MetaCollection2.mdf"
:setvar PrimaryLogFilePhysicalName "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MetaCollection2_log.ldf"

USE [master]

GO

:on error exit

IF  (DB_ID(N'$(DatabaseName)') IS NOT NULL
    AND DATABASEPROPERTYEX(N'$(DatabaseName)','Status') <> N'ONLINE')
BEGIN
    RAISERROR(N'The state of the target database, %s, is not set to ONLINE. To deploy to this database, its state must be set to ONLINE.', 16, 127,N'$(DatabaseName)') WITH NOWAIT
    RETURN
END
GO

:on error exit

IF (DB_ID(N'$(DatabaseName)') IS NOT NULL) 
BEGIN
    ALTER DATABASE [$(DatabaseName)]
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE [$(DatabaseName)];
END
GO
CREATE DATABASE [$(DatabaseName)] ON ( NAME = N'PrimaryFileName', FILENAME = N'$(PrimaryFilePhysicalName)') LOG ON ( NAME = N'PrimaryLogFileName', FILENAME = N'$(PrimaryLogFilePhysicalName)') COLLATE SQL_Latin1_General_CP1_CI_AS 

GO

:on error resume
     
EXEC sp_dbcmptlevel N'$(DatabaseName)', 90

GO

IF EXISTS (SELECT 1 FROM [sys].[databases] WHERE [name] = N'$(DatabaseName)') 
    ALTER DATABASE [$(DatabaseName)] SET  
	ALLOW_SNAPSHOT_ISOLATION OFF
GO

IF EXISTS (SELECT 1 FROM [sys].[databases] WHERE [name] = N'$(DatabaseName)') 
    ALTER DATABASE [$(DatabaseName)] SET  
	READ_COMMITTED_SNAPSHOT OFF
GO

IF EXISTS (SELECT 1 FROM [sys].[databases] WHERE [name] = N'$(DatabaseName)') 
    ALTER DATABASE [$(DatabaseName)] SET  
	MULTI_USER,
	CURSOR_CLOSE_ON_COMMIT OFF,
	CURSOR_DEFAULT LOCAL,
	AUTO_CLOSE OFF,
	AUTO_CREATE_STATISTICS ON,
	AUTO_SHRINK OFF,
	AUTO_UPDATE_STATISTICS ON,
	AUTO_UPDATE_STATISTICS_ASYNC ON,
	ANSI_NULL_DEFAULT ON,
	ANSI_NULLS ON,
	ANSI_PADDING ON,
	ANSI_WARNINGS ON,
	ARITHABORT ON,
	CONCAT_NULL_YIELDS_NULL ON,
	NUMERIC_ROUNDABORT OFF,
	QUOTED_IDENTIFIER ON,
	RECURSIVE_TRIGGERS OFF,
	RECOVERY FULL,
	PAGE_VERIFY NONE,
	DISABLE_BROKER,
	PARAMETERIZATION SIMPLE
	WITH ROLLBACK IMMEDIATE
GO

IF IS_SRVROLEMEMBER ('sysadmin') = 1
BEGIN

IF EXISTS (SELECT 1 FROM [sys].[databases] WHERE [name] = N'$(DatabaseName)') 
    EXEC sp_executesql N'
    ALTER DATABASE [$(DatabaseName)] SET  
	DB_CHAINING OFF,
	TRUSTWORTHY OFF'

END
ELSE
BEGIN
    RAISERROR(N'Unable to modify the database settings for DB_CHAINING or TRUSTWORTHY. You must be a SysAdmin in order to apply these settings.',0,1)
END

GO

USE [$(DatabaseName)]

GO
/*
 Pre-Deployment Script Template							
--------------------------------------------------------------------------------------
 This file contains SQL statements that will be executed before the build script	
 Use SQLCMD syntax to include a file into the pre-deployment script			
 Example:      :r .\filename.sql								
 Use SQLCMD syntax to reference a variable in the pre-deployment script		
 Example:      :setvar TableName MyTable							
               SELECT * FROM [$(TableName)]					
--------------------------------------------------------------------------------------
*/








GO

:on error exit

:on error resume
GO
CREATE USER [StructureService] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[structure]
GO
PRINT N'Creating full text catalogs'
GO
CREATE FULLTEXT CATALOG [StructureTextIndex];
GO
PRINT N'Creating schemata'
GO
CREATE SCHEMA [application]
AUTHORIZATION [dbo]
GO
CREATE SCHEMA [structure]
AUTHORIZATION [dbo]
GO
PRINT N'Creating [structure].[EventType]'
GO
CREATE TABLE [structure].[EventType]
(
[Id] [int] NOT NULL IDENTITY(1, 1),
[Name] [varchar] (50) NOT NULL,
[Description] [varchar] (255) NULL
) ON [PRIMARY]
GO
PRINT N'Creating primary key [PK_EventType] on [structure].[EventType]'
GO
ALTER TABLE [structure].[EventType] ADD CONSTRAINT [PK_EventType] PRIMARY KEY CLUSTERED  ([Id]) ON [PRIMARY]
GO
PRINT N'Creating [structure].[ItemProperty]'
GO
CREATE TABLE [structure].[ItemProperty]
(
[ItemId] [uniqueidentifier] NOT NULL,
[Version] [int] NOT NULL,
[KeyName] [varchar] (50) NOT NULL,
[KeyValue] [varchar] (1024) NULL,
[RowIdentifier] [uniqueidentifier] NOT NULL ROWGUIDCOL CONSTRAINT [DF_ItemProperties_RowId] DEFAULT (newid())
) ON [PRIMARY]
GO
PRINT N'Creating primary key [PK_ItemProperties] on [structure].[ItemProperty]'
GO
ALTER TABLE [structure].[ItemProperty] ADD CONSTRAINT [PK_ItemProperties] PRIMARY KEY CLUSTERED  ([ItemId], [Version], [KeyName]) ON [PRIMARY]
GO
PRINT N'Creating index [ItemPropertyRowIndex] on [structure].[ItemProperty]'
GO
CREATE UNIQUE NONCLUSTERED INDEX [ItemPropertyRowIndex] ON [structure].[ItemProperty] ([RowIdentifier]) ON [PRIMARY]
GO
CREATE FULLTEXT INDEX ON [structure].[ItemProperty] KEY INDEX [ItemPropertyRowIndex] ON [StructureTextIndex]
GO
ALTER FULLTEXT INDEX ON [structure].[ItemProperty] ADD (KeyValue LANGUAGE 1033)
GO
ALTER FULLTEXT INDEX ON [structure].[ItemProperty] ENABLE
GO
PRINT N'Creating [structure].[ItemVersion]'
GO
CREATE TABLE [structure].[ItemVersion]
(
[ItemId] [uniqueidentifier] NOT NULL,
[Version] [int] NOT NULL,
[LeftVersion] [int] NULL,
[RightVersion] [int] NULL,
[Comment] [varchar] (255) NULL,
[Name] [varchar] (255) NOT NULL,
[MimeType] [varchar] (50) NOT NULL,
[ProviderTypeId] [uniqueidentifier] NOT NULL,
[CreatedBy] [varchar] (255) NOT NULL,
[LastModified] [datetime] NOT NULL,
[RowIdentifier] [uniqueidentifier] NOT NULL ROWGUIDCOL
) ON [PRIMARY]
GO
PRINT N'Creating primary key [PK_ItemVersion] on [structure].[ItemVersion]'
GO
ALTER TABLE [structure].[ItemVersion] ADD CONSTRAINT [PK_ItemVersion] PRIMARY KEY CLUSTERED  ([ItemId], [Version]) ON [PRIMARY]
GO
PRINT N'Creating index [ItemVersionRowIndex] on [structure].[ItemVersion]'
GO
CREATE UNIQUE NONCLUSTERED INDEX [ItemVersionRowIndex] ON [structure].[ItemVersion] ([RowIdentifier]) ON [PRIMARY]
GO
CREATE FULLTEXT INDEX ON [structure].[ItemVersion] KEY INDEX [ItemVersionRowIndex] ON [StructureTextIndex]
GO
ALTER FULLTEXT INDEX ON [structure].[ItemVersion] ADD (Comment LANGUAGE 1033)
GO
ALTER FULLTEXT INDEX ON [structure].[ItemVersion] ADD (Name LANGUAGE 1033)
GO
ALTER FULLTEXT INDEX ON [structure].[ItemVersion] ADD (MimeType LANGUAGE 1033)
GO
ALTER FULLTEXT INDEX ON [structure].[ItemVersion] ENABLE
GO
PRINT N'Creating [structure].[RelationshipType]'
GO
CREATE TABLE [structure].[RelationshipType]
(
[Id] [uniqueidentifier] NOT NULL CONSTRAINT [DF__Relationship__Id__09DE7BCC] DEFAULT (newid()),
[Name] [varchar] (50) NULL
) ON [PRIMARY]
GO
PRINT N'Creating primary key [PK_RelationshipType] on [structure].[RelationshipType]'
GO
ALTER TABLE [structure].[RelationshipType] ADD CONSTRAINT [PK_RelationshipType] PRIMARY KEY CLUSTERED  ([Id]) ON [PRIMARY]
GO
PRINT N'Creating [structure].[ItemEvent]'
GO
CREATE TABLE [structure].[ItemEvent]
(
[Id] [int] NOT NULL IDENTITY(1, 1),
[ItemId] [uniqueidentifier] NOT NULL,
[Version] [int] NULL,
[EventDate] [datetime] NOT NULL CONSTRAINT [DF__ItemEvent__Event__7E6CC920] DEFAULT (getdate()),
[EventTypeId] [int] NOT NULL,
[Message] [varchar] (512) NULL
) ON [PRIMARY]
GO
PRINT N'Creating primary key [PK_EventLog] on [structure].[ItemEvent]'
GO
ALTER TABLE [structure].[ItemEvent] ADD CONSTRAINT [PK_EventLog] PRIMARY KEY CLUSTERED  ([Id]) ON [PRIMARY]
GO
PRINT N'Creating [structure].[Item]'
GO
CREATE TABLE [structure].[Item]
(
[Id] [uniqueidentifier] NOT NULL CONSTRAINT [DF__Item__Id__014935CB] DEFAULT (newid()),
[ParentId] [uniqueidentifier] NULL,
[ExternalId] [varchar] (100) NULL,
[PublishedVersion] [int] NULL
) ON [PRIMARY]
GO
PRINT N'Creating primary key [PK_Item] on [structure].[Item]'
GO
ALTER TABLE [structure].[Item] ADD CONSTRAINT [PK_Item] PRIMARY KEY CLUSTERED  ([Id]) ON [PRIMARY]
GO
PRINT N'Creating [structure].[ItemClaim]'
GO
CREATE TABLE [structure].[ItemClaim]
(
[Id] [uniqueidentifier] NOT NULL CONSTRAINT [DF__ItemClaim__Id__07020F21] DEFAULT (newid()),
[InheritedFrom] [uniqueidentifier] NULL,
[ItemId] [uniqueidentifier] NOT NULL,
[ClaimType] [varchar] (255) NOT NULL,
[ClaimValue] [varchar] (50) NULL,
[AccessFlags] [int] NOT NULL CONSTRAINT [DF__ItemClaim__Acces__07F6335A] DEFAULT ((0))
) ON [PRIMARY]
GO
PRINT N'Creating primary key [PK_ItemClaim] on [structure].[ItemClaim]'
GO
ALTER TABLE [structure].[ItemClaim] ADD CONSTRAINT [PK_ItemClaim] PRIMARY KEY CLUSTERED  ([Id]) ON [PRIMARY]
GO
PRINT N'Creating [structure].[ItemResource]'
GO
CREATE TABLE [structure].[ItemResource]
(
[ItemId] [uniqueidentifier] NOT NULL,
[Version] [int] NOT NULL,
[FileExtension] [varchar] (10) NULL,
[Resource] [varbinary] (max) NULL,
[RowIdentifier] [uniqueidentifier] NOT NULL ROWGUIDCOL CONSTRAINT [DF_ItemResource_RowId] DEFAULT (newid())
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
PRINT N'Creating primary key [PK_ItemResource] on [structure].[ItemResource]'
GO
ALTER TABLE [structure].[ItemResource] ADD CONSTRAINT [PK_ItemResource] PRIMARY KEY CLUSTERED  ([ItemId], [Version]) ON [PRIMARY]
GO
PRINT N'Creating index [ItemResourceRowIndex] on [structure].[ItemResource]'
GO
CREATE UNIQUE NONCLUSTERED INDEX [ItemResourceRowIndex] ON [structure].[ItemResource] ([RowIdentifier]) ON [PRIMARY]
GO
CREATE FULLTEXT INDEX ON [structure].[ItemResource] KEY INDEX [ItemResourceRowIndex] ON [StructureTextIndex]
GO
ALTER FULLTEXT INDEX ON [structure].[ItemResource] ADD (Resource TYPE COLUMN FileExtension LANGUAGE 1033)
GO
ALTER FULLTEXT INDEX ON [structure].[ItemResource] ENABLE
GO
PRINT N'Creating [application].[ProviderType]'
GO
CREATE TABLE [application].[ProviderType]
(
[Id] [uniqueidentifier] NOT NULL CONSTRAINT [DF__ProviderType__Id__7C8480AE] DEFAULT (newid()),
[Name] [varchar] (50) NOT NULL,
[Description] [varchar] (255) NULL,
[AssemblyName] [varchar] (255) NOT NULL,
[TypeName] [varchar] (255) NOT NULL,
[Config] [varchar] (max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
PRINT N'Creating primary key [PK_ProviderType] on [application].[ProviderType]'
GO
ALTER TABLE [application].[ProviderType] ADD CONSTRAINT [PK_ProviderType] PRIMARY KEY CLUSTERED  ([Id]) ON [PRIMARY]
GO
PRINT N'Creating [structure].[ItemRelationship]'
GO
CREATE TABLE [structure].[ItemRelationship]
(
[ItemId] [uniqueidentifier] NOT NULL,
[Version] [int] NOT NULL,
[RelationshipTypeId] [uniqueidentifier] NOT NULL,
[TargetItem] [varchar] (512) NOT NULL,
[State] [varchar] (1024) NULL,
[RowIdentifier] [uniqueidentifier] NOT NULL ROWGUIDCOL CONSTRAINT [DF_ItemRelationship_RowId] DEFAULT (newid())
) ON [PRIMARY]
GO
PRINT N'Creating primary key [PK_ItemRelationship] on [structure].[ItemRelationship]'
GO
ALTER TABLE [structure].[ItemRelationship] ADD CONSTRAINT [PK_ItemRelationship] PRIMARY KEY CLUSTERED  ([ItemId], [Version], [RelationshipTypeId], [TargetItem]) ON [PRIMARY]
GO
PRINT N'Adding foreign keys to [structure].[ItemEvent]'
GO
ALTER TABLE [structure].[ItemEvent] ADD
CONSTRAINT [FK_Event_EventType] FOREIGN KEY ([EventTypeId]) REFERENCES [structure].[EventType] ([Id]),
CONSTRAINT [FK_Event_Item] FOREIGN KEY ([ItemId]) REFERENCES [structure].[Item] ([Id])
GO
PRINT N'Adding foreign keys to [structure].[Item]'
GO
ALTER TABLE [structure].[Item] ADD
CONSTRAINT [FK_Item_Item] FOREIGN KEY ([ParentId]) REFERENCES [structure].[Item] ([Id])
GO
PRINT N'Adding foreign keys to [structure].[ItemClaim]'
GO
ALTER TABLE [structure].[ItemClaim] ADD
CONSTRAINT [FK_ItemClaim_Item] FOREIGN KEY ([ItemId]) REFERENCES [structure].[Item] ([Id]),
CONSTRAINT [FK_ItemClaim_ItemClaim] FOREIGN KEY ([InheritedFrom]) REFERENCES [structure].[ItemClaim] ([Id])
GO
PRINT N'Adding foreign keys to [structure].[ItemVersion]'
GO
ALTER TABLE [structure].[ItemVersion] ADD
CONSTRAINT [FK_ItemVersion_Item] FOREIGN KEY ([ItemId]) REFERENCES [structure].[Item] ([Id]) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT [FK_ItemVersion_ProviderType] FOREIGN KEY ([ProviderTypeId]) REFERENCES [application].[ProviderType] ([Id])
GO
PRINT N'Adding foreign keys to [structure].[ItemProperty]'
GO
ALTER TABLE [structure].[ItemProperty] ADD
CONSTRAINT [FK_ItemProperties_ItemVersion] FOREIGN KEY ([ItemId], [Version]) REFERENCES [structure].[ItemVersion] ([ItemId], [Version])
GO
PRINT N'Adding foreign keys to [structure].[ItemRelationship]'
GO
ALTER TABLE [structure].[ItemRelationship] ADD
CONSTRAINT [FK_ItemRelationship_ItemVersion] FOREIGN KEY ([ItemId], [Version]) REFERENCES [structure].[ItemVersion] ([ItemId], [Version]),
CONSTRAINT [FK_ItemRelationship_RelationshipType] FOREIGN KEY ([RelationshipTypeId]) REFERENCES [structure].[RelationshipType] ([Id])
GO
PRINT N'Adding foreign keys to [structure].[ItemResource]'
GO
ALTER TABLE [structure].[ItemResource] ADD
CONSTRAINT [FK_ItemResource_ItemVersion] FOREIGN KEY ([ItemId], [Version]) REFERENCES [structure].[ItemVersion] ([ItemId], [Version])
GO
PRINT N'Creating extended properties'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Parent child heirarchy', 'SCHEMA', N'structure', 'TABLE', N'Item', 'CONSTRAINT', N'FK_Item_Item'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Permissions Record Idenitifier', 'SCHEMA', N'structure', 'TABLE', N'ItemClaim', 'COLUMN', N'Id'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Permissions Record Parent Reference', 'SCHEMA', N'structure', 'TABLE', N'ItemClaim', 'COLUMN', N'InheritedFrom'
GO
EXEC sp_addextendedproperty N'MS_Description', N' Item this record applies to', 'SCHEMA', N'structure', 'TABLE', N'ItemClaim', 'COLUMN', N'ItemId'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Unique ClaimType to demand  in order to grant the AccessFlags to the ClaimPrincipal for the Item', 'SCHEMA', N'structure', 'TABLE', N'ItemClaim', 'COLUMN', N'ClaimType'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Value of the ClaimType property that must be present for ClaimType for this record to apply to a ClaimPrincipal', 'SCHEMA', N'structure', 'TABLE', N'ItemClaim', 'COLUMN', N'ClaimValue'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Binary flags indicating the permission rights associated with this record', 'SCHEMA', N'structure', 'TABLE', N'ItemClaim', 'COLUMN', N'AccessFlags'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Version Number', 'SCHEMA', N'structure', 'TABLE', N'ItemResource', 'COLUMN', N'Version'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Version Number', 'SCHEMA', N'structure', 'TABLE', N'ItemVersion', 'COLUMN', N'Version'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Parent Version this Version drives from (0 = no parent)', 'SCHEMA', N'structure', 'TABLE', N'ItemVersion', 'COLUMN', N'LeftVersion'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Optional second Version merged with LeftVersion when creating this Version', 'SCHEMA', N'structure', 'TABLE', N'ItemVersion', 'COLUMN', N'RightVersion'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Optional comment added when this version was created', 'SCHEMA', N'structure', 'TABLE', N'ItemVersion', 'COLUMN', N'Comment'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Name used to identify this item in the heirarchy.  Used as a path segment in the URI', 'SCHEMA', N'structure', 'TABLE', N'ItemVersion', 'COLUMN', N'Name'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Multipurpose Internet Mail Extensions (content type of the item)', 'SCHEMA', N'structure', 'TABLE', N'ItemVersion', 'COLUMN', N'MimeType'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Type of Resource Provider for this  Item', 'SCHEMA', N'structure', 'TABLE', N'ItemVersion', 'COLUMN', N'ProviderTypeId'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Principal who created this version of the  Item', 'SCHEMA', N'structure', 'TABLE', N'ItemVersion', 'COLUMN', N'CreatedBy'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Last Modified Time for this  Version.  Note that the last modified time of the resource may be different than the heirarchy data', 'SCHEMA', N'structure', 'TABLE', N'ItemVersion', 'COLUMN', N'LastModified'
GO

GO
/*
Post-Deployment Script Template							
--------------------------------------------------------------------------------------
 This file contains SQL statements that will be appended to the build script		
 Use SQLCMD syntax to include a file into the post-deployment script			
 Example:      :r .\filename.sql								
 Use SQLCMD syntax to reference a variable in the post-deployment script		
 Example:      :setvar TableName MyTable							
               SELECT * FROM [$(TableName)]					
--------------------------------------------------------------------------------------
*/








